package mailsystem.persistance.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import mailsystem.Mailbox;
import mailsystem.persistance.MailBoxPersistance;
import mailsystem.persistance.MailsystemPersistance;


public class MailBoxDB extends MailSystemDB implements MailBoxPersistance {
	
	public MailBoxDB(MailsystemPersistance repo) {
		super(repo);		
	}

	@Override
	public void add(Mailbox box) {
		String sql = "INSERT INTO MailBox (boxId, name, greeting, passcode) VALUES (?, ?, ?, ?)";
		PreparedStatement statement = connection.prepareStatement(sql);
		
		try {
			statement.setInt(1, box.getBoxId());
			statement.setString(2, box.getName());
			statement.setString(3, box.getGreeting());
			statement.setString(4, box.getPasscode());
			
			statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			try {
				statement.close();				
			} catch (SQLException sqlException2) {
				sqlException2.printStackTrace();
			}
		}
	}

	@Override
	public void delete(Mailbox box) {
		String sql = "DELETE FROM MailBox WHERE " +
						"boxId = ?";
		PreparedStatement statement = connection.prepareStatement(sql);
		
		try {
			statement.setInt(1, box.getBoxId());
			statement.executeUpdate();	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			try {
				statement.close();				
			} catch (SQLException sqlException2) {
				sqlException2.printStackTrace();
			}
		}
	}

	@Override
	public void update(Mailbox box) {
		String sql = "UPDATE MailBox SET " +
				"boxId = ?, " + 
				"name = ?, " +
				"greeting = ?, " +
				"passcode = ? " +
				"WHERE boxId = ?";
		PreparedStatement statement = connection.prepareStatement(sql);
		
		try {
			statement.setInt(1, box.getBoxId());
			statement.setString(2, box.getName());
			statement.setString(3, box.getGreeting());
			statement.setString(4, box.getPasscode());
			statement.setInt(5, box.getBoxId());
			
			statement.executeUpdate();	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			try {
				statement.close();				
			} catch (SQLException sqlException2) {
				sqlException2.printStackTrace();
			}
		}
	}

	@Override
	public List<Mailbox> listAll()  {
		String sql = "SELECT * FROM MailBox";
		PreparedStatement statement = connection.prepareStatement(sql);
		ArrayList<Mailbox> resp = new ArrayList<Mailbox>();
		
		ResultSet result;
		try {
			result = statement.executeQuery();		
			
			while (result.next())
			{
				resp.add( new Mailbox(
						result.getInt("boxId"),
						result.getString("passcode"), 
						result.getString("greeting"),						
						result.getString("name"), repository));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			try {
				statement.close();				
			} catch (SQLException sqlException2) {
				sqlException2.printStackTrace();
			}
		}
		
		return resp;
	}

}
